package com.music.excel;

import com.music.entity.CmUser;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

@Service
public class WriteXLS {
    public void exportExcel(List<CmUser> list) {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook ();
        HSSFSheet sheet = hssfWorkbook.createSheet ();
        HSSFRow titleRow = sheet.createRow (0);
        titleRow.createCell (0).setCellValue ("用户编号");
        titleRow.createCell (1).setCellValue ("用户姓名");
        titleRow.createCell (2).setCellValue ("用户密码");
        titleRow.createCell (3).setCellValue ("用户手机");
        titleRow.createCell (4).setCellValue ("用户地址");
        titleRow.createCell (5).setCellValue ("用户生日");
        titleRow.createCell (6).setCellValue ("用户签名");
        titleRow.createCell (7).setCellValue ("用户头像");
        titleRow.createCell (8).setCellValue ("用户性别");
        titleRow.createCell (9).setCellValue ("vip");
        for (CmUser user : list) {
            //获取当前最大的行
            int lastRowNum = sheet.getLastRowNum ();
            //新创建一行操作
            HSSFRow dataRow = sheet.createRow (lastRowNum + 1);
            dataRow.createCell (0).setCellValue (user.getuId());
            dataRow.createCell (1).setCellValue (user.getuName()==null?"":user.getuName ());
            dataRow.createCell (2).setCellValue (user.getuPass()==null?"":user.getuPass ());
            dataRow.createCell (3).setCellValue (user.getuTel()==null?"":user.getuTel ());
            dataRow.createCell (4).setCellValue (user.getuAddr()==null?"":user.getuAddr ());
            dataRow.createCell (5).setCellValue (new SimpleDateFormat("yyyy-MM-dd").format(user.getuBirth()!=null?user.getuBirth():new Date()));
            dataRow.createCell (6).setCellValue (user.getuDesc()==null?"":user.getuDesc ());
            dataRow.createCell (7).setCellValue (user.getuPic()==null?null:user.getuPic ());
            dataRow.createCell (8).setCellValue (user.getuSex()==null?"":user.getuSex ());
            dataRow.createCell (9).setCellValue (user.getuStatus()==null?0:user.getuStatus ());
        }
        String fileName = "用户.xls";
        String dirPath="E:\\poi\\excel\\";
        try {
            File file = new File(dirPath);
            if (!file.exists()) {
                file.mkdirs();
            }
            FileOutputStream fileOut = new FileOutputStream (dirPath+fileName);
            hssfWorkbook.write (fileOut);
            hssfWorkbook.close ();
        } catch (FileNotFoundException e) {
            e.printStackTrace ();
        } catch (IOException e) {
            e.printStackTrace ();
        }
    }
}
